import time

from Base.PublicFunction import read_excel, write_data_to_excel
from Base.collect_info import info_collect
import pymysql
from pymysql.cursors import SSDictCursor

class mysqldb():
    def __init__(self):  # 连接数据库
        self.con = pymysql.connect(host='localhost', user='root', password='123456', database='woniuboss', port=3306)
        self.cursor = self.con.cursor(SSDictCursor)

    def getdata_to_db(self):
        results = info_collect.get_data()

        for  detail in results[8]:
            date_time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(results[4]))
            date_time2 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(results[5]))
            date_time3 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(detail.get('start_time')))
            date_time4 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(detail.get('end_time')))
            sql1 = """insert into woniuboss(batch_info,total_excount,success_cases,failed_cases,percent,start_time,end_time,count_time,case_id,case_start_time,case_end_time,case_count_time,actual_res,failed_info) values('%s',%d,%d,%d,'%s',str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s'),str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s'),'%s','%s',str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s'),str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s'),'%s','%s',"%s")""" % \
                   (results[7],results[0],results[1],results[2],results[3],date_time1,date_time2,results[6],detail.get('case_id'),date_time3,date_time4,detail.get('duration_time'),detail.get('autual_res'),detail.get('fail_info'))
            print(sql1)
            self.cursor.execute(sql1)
            self.con.commit()

            excel_datas = read_excel()
            i=0
            for data in excel_datas:
                i +=1
                if data[0] == "":
                    break
            # print(i)

            # print(results[7])
            write_data_to_excel('woniuboss.xls','Sheet1',i-1,0,results[7])
            write_data_to_excel('woniuboss.xls','Sheet1',i-1,1,detail.get('case_id'))



    def getexcel_to_db(self):
        excel_datas = read_excel()
        for data in excel_datas:
            sql1 = """update woniuboss set case_module = '%s' where batch_info = '%s' and case_id = '%s'
            """%(data[2],data[0],data[1])
            sql2 = """update woniuboss set case_desc = '%s' where batch_info = '%s' and case_id = '%s'
            """%(data[3],data[0],data[1])
            sql3 = """update woniuboss set opetator = '%s' where batch_info = '%s' and case_id = '%s'
            """%(data[4],data[0],data[1])
            self.cursor.execute(sql1)
            self.cursor.execute(sql2)
            self.cursor.execute(sql3)
            self.con.commit()


    def read_data(self):
        sql = """
        select * from woniuboss
        """
        self.cursor.execute(sql)
        result_list = []
        for result in self.cursor.fetchall():
            result_list.append(result)

        return result_list

    def __del__(self):
        self.con.close()


if __name__ == '__main__':
    db = mysqldb()
    db.getdata_to_db()   #写入数据库
    # db.getexcel_to_db()  #写入excel
    # db.read_data()   #读取数据库
